package edu.example.Absence.Dao;

import edu.example.Absence.bean.absence;
import edu.example.bean.buildings;
import edu.example.house.serlvet.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class absenceDao {
    DataSource dataSource = C3P0Utils.getDataSource();
    QueryRunner queryRunner = new QueryRunner(dataSource);
    public List<absence> getList(){
        String sql = "select * from less";
        BeanListHandler beanListHandler = new BeanListHandler(absence.class);
        List<absence> list=null;
        try {
            list = (ArrayList<absence>) queryRunner.query(sql,beanListHandler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public List<absence> List(int user_id){
        String sql = "select * from less where user_id=?";
        BeanListHandler beanListHandler = new BeanListHandler(absence.class);
        List<absence> list=null;
        try {
            list = (ArrayList<absence>) queryRunner.query(sql,beanListHandler,user_id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public Integer absenceAdd(Long lessdate,int user_id,String name,String gender,String building_card,String room_num){
        String sql = "insert into less(lessdate,user_id,name,gender,building_card,room_num) values(?,?,?,?,?,?)";
        Integer affectrows=-1;
        try {
            affectrows=queryRunner.update(sql,lessdate,user_id,name,gender,building_card,room_num);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectrows;
    }
    public List<buildings> getAllBuildings() {
        String sql = "SELECT BuildingCard FROM buildings";
        BeanListHandler beanListHandler = new BeanListHandler(buildings.class);
        List<buildings> list=null;
        try {
            list = (ArrayList<buildings>) queryRunner.query(sql,beanListHandler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public List<absence> findById(int id){
        String sql = "select * from less WHERE user_id = ?";
        BeanListHandler beanListHandler = new BeanListHandler(absence.class);
        List<absence> list=null;
        try {
            list = (ArrayList<absence>) queryRunner.query(sql,beanListHandler,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    public Integer absenceUpdate(int id, String building_card, String room_num) {
        String sql = "update less set building_card=?, room_num=? where user_id=?";
        Integer affectrows = -1;
        try {
            affectrows = queryRunner.update(sql, building_card, room_num, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectrows;
    }
    public Integer absenceDelete(int id) {
        String sql = "delete from less where user_id=?";
        Integer affectrows = -1;
        try {
            affectrows = queryRunner.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return affectrows;
    }
}
